/**
 * 2009/12/11
 */
package com.googlecode.derby.customize;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.googlecode.derby.customize.annotation.Column;
import com.googlecode.derby.customize.exception.SqlMakerException;

/**
 * 取得・登録・更新・削除などSQL文の基本動作のみを定義する抽象クラス.
 * 
 * @author o.tanaka
 * @version 2009/12/11
 */
public class DerbySqlMaker extends SqlMaker {

    /** 条件式. {@value} */
    public static final String EQUALS = "=";
    /** 条件式. {@value} */
    public static final String NOTEQUALS = "<>";
    /** 条件式. {@value} */
    public static final String OVER = ">=";
    /** 条件式. {@value} */
    public static final String NOTOVER = "<=";
    /** 条件式. {@value} */
    public static final String MORE = ">";
    /** 条件式. {@value} */
    public static final String LESS = "<";
    /** 条件式. {@value} */
    public static final String IN = "in";
    /** 条件式. {@value} */
    public static final String OUT = "out";
    /** 条件式. {@value} */
    public static final String INOUT = "inout";
    /** 条件式. {@value} */
    public static final String LIKE = "like";
    /** 条件式. {@value} */
    public static final String ISNULL = "is null";
    /** 条件式. {@value} */
    public static final String ISNOTNULL = "is not null";
    /** 条件式. {@value} */
    public static final String WHERE = "where";
    /** 条件式. {@value} */
    public static final String AND = "and";
    /** 条件式. {@value} */
    public static final String OR = "or";
    /** 条件式. {@value} */
    public static final String UNION = "union";
    /** 条件式. {@value} */
    public static final String UNION_ALL = "union all";
    /** 条件式. {@value} */
    public static final String JOIN = "join";
    /** 条件式. {@value} */
    public static final String GROUP_BY = "group by";
    /** 条件式. {@value} */
    public static final String ORDER_BY = "order by";
    /** 条件式. {@value} */
    public static final String SYSDATE = "sysdate";
    /** 条件式. {@value} */
    public static final String TRUNC_SYSDATE = "trunc(sysdate)";
    /** 条件式. {@value} */
    public static final String DESC = "desc";
    /** 条件式. {@value} */
    public static final String ASC = "asc";

    /**
     * 初期化コンストラクター.
     */
    public DerbySqlMaker() {
        super();
    }

    /**
     * @param aTableName
     *            table name
     * @param aColumnz
     *            List<ColumnData>
     * @param aObject
     *            data class
     * @return sql
     */
    @Override
    String addWhere(
            final String aTableName,
            final List<ColumnData> aColumnz,
            final Object aObject) {
        try {
            final StringBuilder where = new StringBuilder();
            final StringBuilder columnSql =
                this.autoWhereColumn(aColumnz, aObject);
            where.append(columnSql.toString());
            return where.toString();
        } catch (final SecurityException e) {
            throw new SqlMakerException(
                    "select error:",
                    this, e);
        } catch (final IllegalArgumentException e) {
            throw new SqlMakerException(
                    "select error:",
                    this, e);
        }
    }

    /**
     * @param aColumnz
     *            List<ColumnData>
     * @param aObject
     *            data class
     * @return sql
     */
    private StringBuilder autoWhereColumn(
            final List<ColumnData> aColumnz,
            final Object aObject) {
        try {
            final StringBuilder columnSql = new StringBuilder();
            int columnCount = 1;
            for (final ColumnData column : aColumnz) {
                final Method[] methodz =
                    aObject.getClass().getMethods();
                final Object value =
                    this.getColumnDataValue(aObject, column, methodz);
                if (value != null) {
                    if (StringUtils.isNotEmpty(column.getColumnName())) {
                        if (columnSql.length() > 0) {
                            columnSql.append("AND ");
                        }
                        columnSql.append(column.getColumnName());
                        columnSql.append(" = ?");
                        this.getSelectBindCountMap().put(
                                column.getColumnName(),
                                Integer.valueOf(columnCount++));
                    }
                }
            }
            return columnSql;
        } catch (final SecurityException e) {
            throw new SqlMakerException(
                    "autoWhereColumn error:",
                    this, e);
        }
    }

    /**
     * @param aTableName
     *            table name
     * @param aColumnz
     *            class Field
     * @return sql
     */
    @Override
    String createTable(
            final String aTableName,
            final List<ColumnData> aColumnz) {

        final StringBuilder sql = new StringBuilder("create table ");
        sql.append(aTableName);
        sql.append(" (");
        final StringBuilder columnSql = new StringBuilder();
        final StringBuilder pkSql = new StringBuilder();
        for (final ColumnData column : aColumnz) {
            if (DbConst.PK.equals(column.getPk())) {
                if (pkSql.length() > 0) {
                    pkSql.append(", ");
                }
                pkSql.append(column.getColumnName());
            }
            if (StringUtils.isNotEmpty(column.getColumnName())) {
                if (columnSql.length() > 0) {
                    columnSql.append(", ");
                }
                columnSql.append(column.getColumnName());
                columnSql.append(" " + column.getType());
                if (StringUtils.isNotEmpty(column.getLength())) {
                    columnSql.append("(" + column.getLength() + ")");
                }
                if (DbConst.NOT_NULL.equals(column.getNotNull())) {
                    columnSql.append(" " + column.getNotNull());
                }
            }
        }

        sql.append(columnSql.toString());
        if (pkSql.length() > 0) {
            final StringBuilder pk = new StringBuilder(", PRIMARY KEY (");
            pk.append(pkSql.toString());
            pk.append(")");
            sql.append(pk.toString());
        }
        sql.append(")");
        return sql.toString();
    }

    /**
     * @param aTableName
     *            table name
     * @param aColumnz
     *            List<ColumnData>
     * @return sql
     */
    @Override
    public String delete(
            final String aTableName,
            final List<ColumnData> aColumnz) {
        this.getDeleteBindCountMap().clear();
        this.getAutoSql().setWhere(null);
        final StringBuilder sql =
            new StringBuilder("DELETE FROM " + aTableName);
        return this.wherePk(aColumnz, sql);
    }

    private Object getColumnDataValue(
            final Object aObject,
            final ColumnData column,
            final Method[] methodz) {
        Object value = null;
        try {
            for (final Method method : methodz) {
                final Column c = method.getAnnotation(Column.class);
                if (c != null) {
                    if (column.getColumnName().equals(c.value()[0])) {
                        value = method.invoke(aObject);
                        break;
                    }
                }
            }
        } catch (final IllegalArgumentException e) {
            throw new SqlMakerException(
                    "getColumnDataValue error:",
                    this, e);
        } catch (final IllegalAccessException e) {
            throw new SqlMakerException(
                    "getColumnDataValue error:",
                    this, e);
        } catch (final InvocationTargetException e) {
            throw new SqlMakerException(
                    "getColumnDataValue error:",
                    this, e);
        }
        return value;
    }

    /**
     * @param aTableName
     *            table name
     * @param aColumnz
     *            List<ColumnData>
     * @return sql
     */
    @Override
    String insert(
            final String aTableName,
            final List<ColumnData> aColumnz) {
        this.getInsertBindCountMap().clear();
        final StringBuilder sql =
            new StringBuilder("INSERT INTO " + aTableName);
        final StringBuilder columnSql = new StringBuilder();
        final StringBuilder valuesSql = new StringBuilder("VALUES(");
        int columnCount = 1;
        for (final ColumnData column : aColumnz) {
            if (StringUtils.isNotEmpty(column.getColumnName())) {
                if (columnSql.length() > 0) {
                    columnSql.append(", ");
                    valuesSql.append(", ");
                } else if (columnSql.length() == 0) {
                    columnSql.append(" (");
                }
                columnSql.append(column.getColumnName());
                valuesSql.append("?");
                this.getInsertBindCountMap().put(
                        column.getColumnName(),
                        Integer.valueOf(columnCount++));
            }
        }
        sql.append(columnSql.toString());
        sql.append(") ");
        sql.append(valuesSql.toString());
        sql.append(") ");
        return sql.toString();
    }

    /**
     * @param aTableName
     *            table name
     * @param aColumnz
     *            List<ColumnData>
     * @return sql
     */
    @Override
    String selectPk(final String aTableName, final List<ColumnData> aColumnz) {
        this.getSelectBindCountMap().clear();
        this.getAutoSql().setWhere(null);
        final StringBuilder sql = new StringBuilder(this.select(aTableName));
        return this.wherePk(aColumnz, sql);

    }

    /**
     * @param aTableName
     *            table name
     * @param aColumnz
     *            List<ColumnData>
     * @return sql
     */
    @Override
    String update(
            final String aTableName,
            final List<ColumnData> aColumnz) {
        this.getUpdateBindCountMap().clear();
        this.getAutoSql().setWhere(null);
        final StringBuilder sql = new StringBuilder("UPDATE " + aTableName);
        final StringBuilder columnSql = new StringBuilder();
        final List<String> pkIndex = new ArrayList<String>();
        int columnCount = 1;
        int updateColumnCount = 0;
        for (final ColumnData column : aColumnz) {
            if (DbConst.PK.equals(column.getPk())) {
                this.wherePk(column);
                pkIndex.add(column.getColumnName());
            }
            if (!DbConst.PK.equals(column.getPk())) {
                if (StringUtils.isNotEmpty(column.getColumnName())) {
                    if (columnSql.length() > 0) {
                        columnSql.append(", ");
                    } else if (columnSql.length() == 0) {
                        columnSql.append(" SET ");
                    }
                    columnSql.append(column.getColumnName());
                    columnSql.append(" = ?");
                    this.getUpdateBindCountMap().put(
                            column.getColumnName(),
                            Integer.valueOf(columnCount++));
                    updateColumnCount++;
                }
            }
        }
        this.setUpdateColumnCount(updateColumnCount);
        for (final String pkColumnName : pkIndex) {
            this.getUpdateBindCountMap().put(
                    pkColumnName,
                    Integer.valueOf(columnCount++));
        }

        sql.append(columnSql.toString());
        if (this.getAutoSql().getWhere() != null) {
            sql.append(" " + this.getAutoSql().getWhere());
        }
        sql.append(" ");
        return sql.toString();
    }

    private void wherePk(
            final ColumnData column) {
        final StringBuilder pkSql = new StringBuilder();
        if (StringUtils.isNotEmpty(this.getAutoSql().getWhere())) {
            pkSql.append("AND ");
        } else {
            pkSql.append("WHERE ");
        }
        pkSql.append(column.getColumnName());
        pkSql.append(" = ?");
        String where = this.getAutoSql().getWhere();
        if (StringUtils.isEmpty(where)) {
            where = "";
        }
        this.getAutoSql().setWhere(
                where + " " + pkSql.toString());
    }

    /**
     * @param aColumnz
     * @param sql
     * @return
     */
    private String wherePk(
            final List<ColumnData> aColumnz,
            final StringBuilder sql) {
        int columnCount = 1;
        for (final ColumnData column : aColumnz) {
            if (DbConst.PK.equals(column.getPk())) {
                this.wherePk(column);
                if (sql.toString().matches("^SELECT.*")) {
                    this.getSelectBindCountMap().put(
                            column.getColumnName(),
                            Integer.valueOf(columnCount++));
                } else if (sql.toString().matches("^DELETE.*")) {
                    this.getDeleteBindCountMap().put(
                            column.getColumnName(),
                            Integer.valueOf(columnCount++));
                }
            }
        }

        if (this.getAutoSql().getWhere() != null) {
            sql.append(" " + this.getAutoSql().getWhere());
        }
        sql.append(" ");
        return sql.toString();
    }

}
